package laboratorioV.grupo21.tp4.clientes;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashSet;

public class ClientesFromDB {
	public Cliente getById(String pId){
		PreparedStatement ps = null;		
		Connection con = null;
		Cliente clienteAux = null;
		
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			
			con = DriverManager.getConnection("jdbc:derby:derbyDb/TP6;create=false");
						
			ps = con.prepareStatement("SELECT * FROM CLIENTES WHERE ID_CLIENTE = ?");
			ps.setString(1, pId);
		    java.sql.ResultSet rs = ps.executeQuery();
		      
		    while(rs.next()){
		    	clienteAux = new Cliente(rs.getString("ID_CLIENTE"), rs.getString("NOMBRE"), rs.getString("APELLIDO") );		
		    	return clienteAux;
		    }			
		}
		catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)con.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}
			
		}
		return null;
	}
	public HashSet<Cliente> getClientes(){
		PreparedStatement ps = null;		
		Connection con = null;
		Cliente clienteAux = null;
		HashSet<Cliente> setClientes = new HashSet<Cliente>();
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			
			con = DriverManager.getConnection("jdbc:derby:derbyDb/TP6;create=false");
						
			ps = con.prepareStatement("SELECT * FROM CLIENTES");
			
		    java.sql.ResultSet rs = ps.executeQuery();
		      
		    while(rs.next()){
		    	clienteAux = new Cliente(rs.getString("ID_CLIENTE"), rs.getString("NOMBRE"), rs.getString("APELLIDO") );		
		    	setClientes.add(clienteAux);
		    }			
		    return setClientes;
		}
		catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)con.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}
			
		}
		return null;
	}
	
	public HashSet<Cliente> getClientes(Connection con){
		PreparedStatement ps = null;		
		//Connection con = null;
		Cliente clienteAux = null;
		HashSet<Cliente> setClientes = new HashSet<Cliente>();
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			
			//con = DriverManager.getConnection("jdbc:derby:derbyDb/TP6;create=false");
						
			ps = con.prepareStatement("SELECT * FROM CLIENTES");
			
		    java.sql.ResultSet rs = ps.executeQuery();
		      
		    while(rs.next()){
		    	clienteAux = new Cliente(rs.getString("ID_CLIENTE"), rs.getString("NOMBRE"), rs.getString("APELLIDO") );		
		    	setClientes.add(clienteAux);
		    }			
		    return setClientes;
		}
		catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)con.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}
			
		}
		return null;
	}
	
	
}
